#!/usr/bin/env python3
"""
数据库修复脚本
修复日期格式问题和清理测试数据
"""

import sys
import os
from datetime import datetime, date

# 添加项目根目录到 Python 路径
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

from models.database import DatabaseManager
from config.settings import settings


def fix_date_format():
    """修复数据库中的日期格式问题"""
    print("🔧 开始修复数据库日期格式...")
    
    try:
        db_manager = DatabaseManager(settings.get_database_path())
        
        # 获取所有任务
        query = "SELECT id, due_date, created_at, updated_at FROM tasks"
        tasks = db_manager.execute_query(query)
        
        print(f"📊 找到 {len(tasks)} 个任务需要检查")
        
        fixed_count = 0
        for task in tasks:
            task_id, due_date, created_at, updated_at = task
            
            # 修复 due_date
            if due_date and not _is_valid_date_format(due_date):
                try:
                    # 尝试解析并转换为正确格式
                    parsed_date = _parse_date(due_date)
                    if parsed_date:
                        new_due_date = parsed_date.strftime('%Y-%m-%d')
                        db_manager.execute_update(
                            "UPDATE tasks SET due_date = ? WHERE id = ?",
                            (new_due_date, task_id)
                        )
                        print(f"  ✅ 修复任务 {task_id} 的截止日期: {due_date} -> {new_due_date}")
                        fixed_count += 1
                except Exception as e:
                    print(f"  ❌ 无法修复任务 {task_id} 的截止日期: {e}")
            
            # 修复 created_at
            if created_at and not _is_valid_datetime_format(created_at):
                try:
                    parsed_datetime = _parse_datetime(created_at)
                    if parsed_datetime:
                        new_created_at = parsed_datetime.strftime('%Y-%m-%d %H:%M:%S')
                        db_manager.execute_update(
                            "UPDATE tasks SET created_at = ? WHERE id = ?",
                            (new_created_at, task_id)
                        )
                        print(f"  ✅ 修复任务 {task_id} 的创建时间")
                        fixed_count += 1
                except Exception as e:
                    print(f"  ❌ 无法修复任务 {task_id} 的创建时间: {e}")
            
            # 修复 updated_at
            if updated_at and not _is_valid_datetime_format(updated_at):
                try:
                    parsed_datetime = _parse_datetime(updated_at)
                    if parsed_datetime:
                        new_updated_at = parsed_datetime.strftime('%Y-%m-%d %H:%M:%S')
                        db_manager.execute_update(
                            "UPDATE tasks SET updated_at = ? WHERE id = ?",
                            (new_updated_at, task_id)
                        )
                        print(f"  ✅ 修复任务 {task_id} 的更新时间")
                        fixed_count += 1
                except Exception as e:
                    print(f"  ❌ 无法修复任务 {task_id} 的更新时间: {e}")
        
        print(f"🎉 修复完成！共修复 {fixed_count} 个日期字段")
        
    except Exception as e:
        print(f"❌ 修复失败: {e}")


def _is_valid_date_format(date_str):
    """检查日期格式是否为 YYYY-MM-DD"""
    try:
        datetime.strptime(date_str, '%Y-%m-%d')
        return True
    except:
        return False


def _is_valid_datetime_format(datetime_str):
    """检查日期时间格式是否为 YYYY-MM-DD HH:MM:SS"""
    try:
        datetime.strptime(datetime_str, '%Y-%m-%d %H:%M:%S')
        return True
    except:
        return False


def _parse_date(date_str):
    """尝试解析各种日期格式"""
    formats = [
        '%Y-%m-%d',
        '%Y/%m/%d',
        '%m/%d/%Y',
        '%d/%m/%Y',
        '%Y-%m-%dT%H:%M:%S',
        '%Y-%m-%dT%H:%M:%S.%f'
    ]
    
    for fmt in formats:
        try:
            return datetime.strptime(date_str, fmt).date()
        except:
            continue
    
    return None


def _parse_datetime(datetime_str):
    """尝试解析各种日期时间格式"""
    formats = [
        '%Y-%m-%d %H:%M:%S',
        '%Y-%m-%dT%H:%M:%S',
        '%Y-%m-%dT%H:%M:%S.%f',
        '%Y/%m/%d %H:%M:%S',
        '%m/%d/%Y %H:%M:%S'
    ]
    
    for fmt in formats:
        try:
            return datetime.strptime(datetime_str, fmt)
        except:
            continue
    
    return None


def clean_test_data():
    """清理测试数据"""
    print("🧹 开始清理测试数据...")
    
    try:
        db_manager = DatabaseManager(settings.get_database_path())
        
        # 删除明显的测试数据
        test_patterns = [
            "测试任务",
            "Test Task",
            "新任务",
            "学习React框架",
            "完成项目文档"
        ]
        
        deleted_count = 0
        for pattern in test_patterns:
            result = db_manager.execute_update(
                "DELETE FROM tasks WHERE title LIKE ?",
                (f"%{pattern}%",)
            )
            if result > 0:
                deleted_count += result
                print(f"  ✅ 删除包含 '{pattern}' 的 {result} 个任务")
        
        print(f"🎉 清理完成！删除了 {deleted_count} 类测试数据")
        
    except Exception as e:
        print(f"❌ 清理失败: {e}")


def main():
    """主函数"""
    print("🔧 数据库修复工具")
    print("=" * 40)
    
    choice = input("请选择操作:\n1. 修复日期格式\n2. 清理测试数据\n3. 全部执行\n请输入选择 (1-3): ").strip()
    
    if choice == '1':
        fix_date_format()
    elif choice == '2':
        clean_test_data()
    elif choice == '3':
        fix_date_format()
        print()
        clean_test_data()
    else:
        print("❌ 无效选择")
        return 1
    
    print("\n✅ 操作完成！现在可以重新启动应用程序。")
    return 0


if __name__ == "__main__":
    sys.exit(main())